<%@codepage="65001"%>
<%session.codepage="65001"%>
<!-- #include virtual=/inc/dbConn.asp -->
<!-- #include virtual=/inc/function.asp -->
<!-- #include virtual=/inc/action.header.inc.asp -->

<%
	'## 파라미터 변수 선언부
	stdYear		= Request("findYear")
	stdType		= Request("findType")
	stdField		= Request("findField")
	stdSection	= Request("findSection")

	If stdYear = "" Then stdYear = Left(Date(),4) End If 
	If stdType = "" Then stdType = "10" End If 
	If stdField = "" Then stdField = "prdGrp" End If 
	
	SQLTitle = "-- ■■■■ 통계 관리 그리드 List up ■■■■"
	
	'## 검색조건에 따른 조건절 만들기 
	If stdType <> "" Then 
		addSql = " AND A.type='" & stdType & "' "
	End If 

	If stdSection = "badRate" And (stdType = "10" Or stdType = "20") Then 
		stdCount = "CASE WHEN (SUM(A.goodQty)+SUM(A.badQty)) > 0 THEN ROUND(SUM(A.badQty)/(SUM(A.goodQty)+SUM(A.badQty))*100,2) ELSE 0 END "
		underPoint = 2
		perFlag = "%"
	Else
		stdCount = "SUM(A.goodQty)"
		underPoint = 0
		perFlag = ""
	End If 

	SQL = "SELECT  " & stdField & " prdGrp, [" & stdYear & "-01] M01, [" & stdYear & "-02] M02, [" & stdYear & "-03] M03, [" & stdYear & "-04] M04, [" & stdYear & "-05] M05, [" & stdYear & "-06] M06, " & _
			"	[" & stdYear & "-07] M07, [" & stdYear & "-08] M08, [" & stdYear & "-09] M09, [" & stdYear & "-10] M10,[" & stdYear & "-11] M11, [" & stdYear & "-12] M12 " & _
			"	FROM ( " & _
			"		SELECT C." & stdField & ", LEFT(A.workDt,7) wDt, " & stdCount & " Qty " & _
			"		FROM tblOrderResult A " & _
			"			LEFT JOIN tblOrder B ON A.lotYear=B.lotYear AND A.lotNo=B.lotNo AND A.lotSubNo=B.lotSubNo " & _
			"			LEFT JOIN (SELECT * FROM tblProduct WHERE prdGrp<>'KWT') C ON B.prdCd=C.prdCd " & _
			"		WHERE A.flag='Y' AND B.flag<>'N' AND ISNULL(A.workDt,'')<>'' AND C.prdGrp<>'' " & addSql & _
			"		GROUP BY LEFT(A.workDt,7), C." & stdField & " " & _
			"		UNION ALL " & _
			"		SELECT 'ZZ총량' as prdGrp, LEFT(A.workDt,7) wDt, " & stdCount & " Qty " & _
			"		FROM tblOrderResult A " & _
			"			LEFT JOIN tblOrder B ON A.lotYear=B.lotYear AND A.lotNo=B.lotNo AND A.lotSubNo=B.lotSubNo " & _
			"			LEFT JOIN tblProduct C ON B.prdCd=C.prdCd " & _
			"		WHERE A.flag='Y' AND B.flag<>'N' AND ISNULL(A.workDt,'')<>'' AND C.prdGrp<>'' " & addSql & _
			"		GROUP BY LEFT(A.workDt,7)	" & _
			"	) K " & _
			"	PIVOT ( SUM(QTY) FOR wDt IN ([" & stdYear & "-01] ,[" & stdYear & "-02], [" & stdYear & "-03],[" & stdYear & "-04] ,[" & stdYear & "-05],[" & stdYear & "-06], " & _
			"	[" & stdYear & "-07] ,[" & stdYear & "-08], [" & stdYear & "-09],[" & stdYear & "-10] ,[" & stdYear & "-11], [" & stdYear & "-12] )) pvt " & _
			"	ORDER BY prdGrp	"

'Response.write sql
	If xMode="debug" Then 
		Call showDebug(SQLTitle,SQL) 
	End If 

	Set Rs=Server.CreateObject("ADODB.RecordSet")
	Rs.open SQL, DbCon, 1
	
	Response.write "<Statistics>" & vbcrlf

	If Rs.eof Or Rs.bof Then 
	Else 
		Do Until Rs.eof Or Rs.bof
			
			m01 = "0"
			m02 = "0"
			m03 = "0"
			m04 = "0"
			m05 = "0"
			m06 = "0"
			m07 = "0"
			m08 = "0"
			m09 = "0"
			m10 = "0"
			m11 = "0"
			m12 = "0"
			total = "1"
			If rs("m01") <> "" Then m01 = FormatNumber(rs("m01"),underPoint) End If 
			If rs("m02") <> "" Then m02 = FormatNumber(rs("m02"),underPoint) End If 
			If rs("m03") <> "" Then m03 = FormatNumber(rs("m03"),underPoint) End If 
			If rs("m04") <> "" Then m04 = FormatNumber(rs("m04"),underPoint) End If 
			If rs("m05") <> "" Then m05 = FormatNumber(rs("m05"),underPoint) End If 
			If rs("m06") <> "" Then m06 = FormatNumber(rs("m06"),underPoint) End If 
			If rs("m07") <> "" Then m07 = FormatNumber(rs("m07"),underPoint) End If 
			If rs("m08") <> "" Then m08 = FormatNumber(rs("m08"),underPoint) End If 
			If rs("m09") <> "" Then m09 = FormatNumber(rs("m09"),underPoint) End If 
			If rs("m10") <> "" Then m10 = FormatNumber(rs("m10"),underPoint) End If 
			If rs("m11") <> "" Then m11 = FormatNumber(rs("m11"),underPoint) End If 
			If rs("m12") <> "" Then m12 = FormatNumber(rs("m12"),underPoint) End If 
			total = CDbl(m01)+CDbl(m02)+CDbl(m03)+CDbl(m04)+CDbl(m05)+CDbl(m06)+CDbl(m07)+CDbl(m08)+CDbl(m09)+CDbl(m10)+CDbl(m11)+CDbl(m12)

			If total <> "" Then total = FormatNumber(total,0) End If 
			If underPoint = 2 Then 
				total = "-"
				If m01 = "0.00" Then m01 = "0" End If 
				If m02 = "0.00" Then m02 = "0" End If 
				If m03 = "0.00" Then m03 = "0" End If 
				If m04 = "0.00" Then m04 = "0" End If 
				If m05 = "0.00" Then m05 = "0" End If 
				If m06 = "0.00" Then m06 = "0" End If 
				If m07 = "0.00" Then m07 = "0" End If 
				If m08 = "0.00" Then m08 = "0" End If 
				If m09 = "0.00" Then m09 = "0" End If 
				If m10 = "0.00" Then m10 = "0" End If 
				If m11 = "0.00" Then m11 = "0" End If 
				If m12 = "0.00" Then m12 = "0" End If 
				m01 = m01 & perFlag
				m02 = m02 & perFlag
				m03 = m03 & perFlag
				m04 = m04 & perFlag
				m05 = m05 & perFlag
				m06 = m06 & perFlag
				m07 = m07 & perFlag
				m08 = m08 & perFlag
				m09 = m09 & perFlag
				m10 = m10 & perFlag
				m11 = m11 & perFlag
				m12 = m12 & perFlag
			End If 

			Response.write "	<Stat>" & vbcrlf
				Call mkXMLNode("prdGrp", rs("prdGrp"), 2)
				Call mkXMLNode("m01", m01, 2)
				Call mkXMLNode("m02", m02, 2)
				Call mkXMLNode("m03", m03, 2)
				Call mkXMLNode("m04", m04, 2)
				Call mkXMLNode("m05", m05, 2)
				Call mkXMLNode("m06", m06, 2)
				Call mkXMLNode("m07", m07, 2)
				Call mkXMLNode("m08", m08, 2)
				Call mkXMLNode("m09", m09, 2)
				Call mkXMLNode("m10", m10, 2)
				Call mkXMLNode("m11", m11, 2)
				Call mkXMLNode("m12", m12, 2)
				Call mkXMLNode("total", total, 2)
			Response.write "	</Stat>" & vbcrlf

		Rs.movenext
		Loop 
	End If
	Response.write "</Statistics>" & vbcrlf
'	Call rsWithXML("Save OK!")
%>